﻿
Partial Class WebForm_ManageSell_OutStockExcelUploadV2
    Inherits System.Web.UI.Page
#Region "事件"
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Me.BindType()
            Me.divMatch.Visible = False
            Me.BindFileColumnName()
            Me.BindDDLCustomerGroup()
            Me.BindDDLDivision()
        End If
        Me.Form.Enctype = "multipart/form-data"
    End Sub
    Protected Sub ddlType_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlType.SelectedIndexChanged
        If ddlType.SelectedValue = "SellOut" Then
            lblYMonth.Visible = True
            ddlYMonth.Visible = True
            Me.BindYearMonth()
        Else
            lblYMonth.Visible = False
            ddlYMonth.Visible = False
        End If
    End Sub
    Protected Sub ddlDivision_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlDivision.SelectedIndexChanged
        Me.BindDDLCustomerGroup()
    End Sub
    Protected Sub gvMatch_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvMatch.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            Dim ddlFileColumnName As DropDownList = TryCast(e.Row.FindControl("ddlFileColumnName"), DropDownList)
            ddlFileColumnName.DataTextField = "FileColumnName"
            ddlFileColumnName.DataValueField = "FileColumnName"
            ddlFileColumnName.DataSource = Me.FileColumnName
            ddlFileColumnName.DataBind()
            ddlFileColumnName.Items.Insert(0, MyObj.GetDDLPleaseChooseItem())
        End If
    End Sub

    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        If Not FileUpload1.HasFile Then
            MyObj.ShowMessage("no file")
            Return
        ElseIf IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper() <> ".XLS" AndAlso IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper() <> ".XLSX" Then
            MyObj.ShowMessage("Import failed!!Please select the file extension (xls/ xlsx) file!!")
            Return
        End If

        '上傳暫存檔
        Dim clsRandom As New clsRandom()
        Dim newName As String = Server.MapPath("~/") & "/tmp/" & clsRandom.Generate()
        FileUpload1.PostedFile.SaveAs(newName)
        Me.lblFileName.Text = Me.FileUpload1.PostedFile.FileName.ToString()

        Dim ExcelImportCheck As New ExcelImportCheck()
        Dim dt As New Data.DataTable

        Me.BindDBColumnName()
        '檢查工作表名稱
        Try
            If ExcelImportCheck.LoadData(dt, newName, ddlType.SelectedValue, IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper()) = False Then
                Return
            Else
                Me.ImportData = dt
            End If
        Catch ex As Exception
            MyObj.ShowMessage("Please check the file format!!!")
            Exit Sub
        End Try

        If Me.ImportData.Rows.Count > 0 Then
            Me.FileColumnName.Rows.Clear()
            For i As Integer = Me.ImportData.Columns.Count - 1 To 0 Step -1
                '去除空的Column
                If Me.ImportData.Columns(i).ColumnName.Substring(0, 1) <> "F" Then
                    Dim row As Data.DataRow
                    row = Me.FileColumnName.NewRow()
                    row("FileColumnName") = Me.ImportData.Columns(i).ColumnName
                    Me.FileColumnName.Rows.InsertAt(row, 0)
                Else
                    Me.ImportData.Columns.Remove(Me.ImportData.Columns(i).ColumnName)
                End If

            Next


            Me.divMatch.Visible = True
            Me.gvMatch.DataSource = Me.DBColumnName
            Me.gvMatch.DataBind()

            '幫它預選欄位
            Dim ColumnString As String = ""
            Dim ColumnFstSelect As String = "SAP,貨號,品號"
            Dim ColumnSecSelect As String = "WSN,銷售總量,數量,庫存量,銷售數"
            Dim FstColumnValue As String = ""
            Dim SecColumnValue As String = ""
            For b As Integer = 0 To Me.ImportData.Columns.Count - 1
                ColumnString &= Me.ImportData.Columns(b).ColumnName & ","
            Next

            '去掉最後一個逗號
            ColumnString = ColumnString.Substring(0, ColumnString.Length - 1)

            For Each ColString As String In ColumnString.Split(",")
                For Each SelecFstString As String In ColumnFstSelect.Split(",")
                    If ColString = SelecFstString Then
                        FstColumnValue = SelecFstString
                    End If
                Next

                For Each SelectSecString As String In ColumnSecSelect.Split(",")
                    If ColString = SelectSecString Then
                        SecColumnValue = SelectSecString
                    End If
                Next
            Next


            TryCast(Me.gvMatch.Rows(0).FindControl("ddlFileColumnName"), DropDownList).SelectedValue = FstColumnValue
            TryCast(Me.gvMatch.Rows(1).FindControl("ddlFileColumnName"), DropDownList).SelectedValue = SecColumnValue

        Else
            MyObj.ShowMessage("No Data is available for Import!!")
        End If
    End Sub

    Protected Sub btnSent_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSent.Click
        For i As Integer = 0 To Me.gvMatch.Rows.Count - 1
            Dim ddlFileColumnName As DropDownList = TryCast(Me.gvMatch.Rows(i).FindControl("ddlFileColumnName"), DropDownList)
            If ddlFileColumnName.SelectedValue = "" Then
                MyObj.ShowMessage("Please confirm that your file column name exactly match your system column name!")
                Return
            End If
        Next

        'CustomerGroup必選
        If Me.ddlCustomerGroup.SelectedValue = MyObj.PleaseChooseValue Then
            MyObj.ShowMessage("Please select Customer Group!!")
            Return
        End If

        '組去空值的字串
        Dim FilterValue As String = ""
        For i As Integer = 0 To Me.ImportData.Columns.Count - 1
            If FilterValue = "" Then
                FilterValue &= "convert([" & Me.ImportData.Columns(0).ColumnName.ToString() & "],System.String)<>''"
            Else
                FilterValue &= " or convert([" & Me.ImportData.Columns(i).ColumnName.ToString() & "],System.String)<>''"
            End If
        Next

        '新增Error欄位
        Me.ImportData.Columns.Add("Error", System.Type.GetType("System.String"))

        '開始去空值
        Dim MyDefaultView As Data.DataView = Me.ImportData.DefaultView
        MyDefaultView.RowFilter = FilterValue
        Me.ImportData = MyDefaultView.ToTable()

        Dim sql As New MyStringBuilder
        Dim dat As New clsData
        Dim AuthSapDt As Data.DataTable
        Dim ExistMappingDt As Data.DataTable

        '取出有權限的SapCode資料
        sql.AppendFormat("select distinct SapCode from ProductData pd ")
        '卡權限,有需要再打開
        'sql.AppendFormat("join EmployeeHierarchyMapping EHM on pd.ProdHierarchy=EHM.AuthProdHierarchy and EHM.EmployeeID='{0}' ", clsAuth.GetUserID())
        AuthSapDt = dat.GetData(sql)
        AuthSapDt.PrimaryKey = New DataColumn() {AuthSapDt.Columns(0)}

        '取出所有SapCode資料
        Dim AllSapCodeDT As Data.DataTable
        sql.Length = 0
        sql.AppendFormat("Select distinct SapCode from ProductData ")
        AllSapCodeDT = dat.GetData(sql)
        AllSapCodeDT.PrimaryKey = New DataColumn() {AllSapCodeDT.Columns(0)}

        '準備已新增的SapCode,WSN Code
        sql.Length = 0
        sql.AppendFormat("Select SapCode,MarketCode from SapCodeMarketCodeMapping ")
        ExistMappingDt = dat.GetData(sql)

        '檢查是否有重複資料(SapCode)
        Dim MatchSapDT As New Data.DataTable
        MatchSapDT.Columns.Add("SapCode", System.Type.GetType("System.String"))
        MatchSapDT.PrimaryKey = New DataColumn() {MatchSapDT.Columns(0)}

        '檢查是否有重複資料(MarketCode)
        Dim MatchMarketDT As New Data.DataTable
        MatchMarketDT.Columns.Add("MarketCode", System.Type.GetType("System.String"))
        MatchMarketDT.PrimaryKey = New DataColumn() {MatchMarketDT.Columns(0)}

        Dim ErrorCount As Integer = 0
        Dim colFstName, colSecName As String
        colFstName = TryCast(Me.gvMatch.Rows(0).FindControl("ddlFileColumnName"), DropDownList).SelectedValue
        colSecName = TryCast(Me.gvMatch.Rows(1).FindControl("ddlFileColumnName"), DropDownList).SelectedValue


        '開始檢查資料
        Dim NewRow As DataRow
        Dim WSNLength As Integer
        Dim MySapRow As DataRow()
        Dim MyMarketRow As DataRow()
        Dim SapCode, MarketCode, ItemValue As String
        For i As Integer = 0 To Me.ImportData.Rows.Count - 1
            If ddlType.SelectedValue = "SCMP" Then
                Me.ImportData.Rows(i)(colFstName) = Replace(Me.ImportData.Rows(i)(colFstName).ToString(), MyObj.GetAreaCode(), "")
                SapCode = Me.ImportData.Rows(i)(colFstName).ToString().Trim()
                MarketCode = Me.ImportData.Rows(i)(colSecName).ToString().Trim()
            Else
                MarketCode = Me.ImportData.Rows(i)(colFstName).ToString().Trim()
            End If

            If ddlType.SelectedValue = "SCMP" Then
                '比對SapCode是否重複
                If MatchSapDT.Rows.Contains(SapCode) Then
                    Me.ImportData.Rows(i)("Error") = "The SAP code is repetition."
                    ErrorCount += 1
                    Continue For
                Else
                    NewRow = MatchSapDT.NewRow()
                    NewRow("SapCode") = SapCode
                    MatchSapDT.Rows.Add(NewRow)
                End If
            End If

            '比對MarketCode是否重複
            If MatchMarketDT.Rows.Contains(MarketCode) Then
                Me.ImportData.Rows(i)("Error") = "The Channel Code is repetition."
                ErrorCount += 1
                Continue For
            Else
                NewRow = MatchMarketDT.NewRow()
                NewRow("MarketCode") = MarketCode
                MatchMarketDT.Rows.Add(NewRow)
            End If

            MySapRow = ExistMappingDt.Select(String.Format("SapCode='{0}'", SapCode))
            MyMarketRow = ExistMappingDt.Select(String.Format("MarketCode='{0}'", MarketCode))
            If ddlType.SelectedValue = "SCMP" Then
                '比對此SapCode是否存在
                If Not AllSapCodeDT.Rows.Contains(SapCode) Then
                    Me.ImportData.Rows(i)("Error") = "The SAP Code dosen't exist."
                    ErrorCount += 1
                    Continue For
                End If

                '比對是否有此SapCode權限
                If Not AuthSapDt.Rows.Contains(SapCode) Then
                    Me.ImportData.Rows(i)("Error") = "The SAP code is out of your scope."
                    ErrorCount += 1
                    Continue For
                End If

                '比對此MarketCode是否已存在,且是否為相同的SapCode
                If MyMarketRow.Length > 0 AndAlso MyMarketRow(0)("SapCode").ToString() <> SapCode Then
                    Me.ImportData.Rows(i)("Error") = "The Channel Code already exist."
                    ErrorCount += 1
                    Continue For
                End If

                '比對WSN Code格式
                If i = 0 Then
                    WSNLength = MarketCode.Length
                Else
                    If MarketCode.Length <> WSNLength Then
                        Me.ImportData.Rows(i)("Error") = "The Channel Code isn't same."
                        ErrorCount += 1
                        Continue For
                    End If
                End If
            Else
                '貨號是否存在
                If MyMarketRow.Length = 0 Then
                    Me.ImportData.Rows(i)("Error") = "The Channel Code dosen't exist."
                    ErrorCount += 1
                    Continue For
                End If
            End If
        Next

        '有異常則不寫入資料,反之則寫入
        If (ErrorCount > 0) Then
            MyObj.ShowMessage("Upload Failed.Data Error Count:" & ErrorCount)
            Me.divMatch.Visible = False
        Else
            If ImportData.Rows.Count > 0 Then
                For a As Integer = 0 To ImportData.Rows.Count - 1
                    ItemValue = 0
                    If ddlType.SelectedValue = "SCMP" Then
                        SapCode = Me.ImportData.Rows(a)(colFstName).ToString().Trim()
                        MarketCode = Me.ImportData.Rows(a)(colSecName).ToString().Trim()
                    Else
                        MarketCode = Me.ImportData.Rows(a)(colFstName).ToString().Trim()
                        ItemValue = Me.ImportData.Rows(a)(colSecName).ToString().Trim()
                    End If
                    MySapRow = ExistMappingDt.Select(String.Format("MarketCode='{0}'", MarketCode))

                    sql.Length = 0
                    Select Case ddlType.SelectedValue
                        Case "SCMP"
                            sql.AppendFormat("update SapCodeMarketCodeMapping Set ")
                            sql.AppendFormat("MarketCode='{0}' ", MarketCode)
                            sql.AppendFormat("where SapCode='{0}' ", SapCode)
                            sql.AppendFormat("and ChannelID='{0}' ", Me.ddlCustomerGroup.SelectedValue)
                            sql.AppendFormat(";")
                            sql.AppendFormat("Insert Into SapCodeMarketCodeMapping(SapCode,MarketCode,ChannelID) ")
                            sql.AppendFormat("select '{0}' ", SapCode)
                            sql.AppendFormat(", '{0}' ", MarketCode)
                            sql.AppendFormat(", '{0}' ", Me.ddlCustomerGroup.SelectedValue)
                            sql.AppendFormat("where not exists ( ")
                            sql.AppendFormat("select * from SapCodeMarketCodeMapping ")
                            sql.AppendFormat("where SapCode='{0}' ", SapCode)
                            sql.AppendFormat("and MarketCode='{0}' ", MarketCode)
                            sql.AppendFormat("and ChannelID='{0}' ) ", Me.ddlCustomerGroup.SelectedValue)
                            sql.AppendFormat(";")
                        Case "SellOut"
                            Dim TableName As String = "SFReport" & ddlYMonth.SelectedValue.Substring(0, 4) & "A"
                            Dim Month As String = ""

                            If ddlYMonth.SelectedValue.Substring(5, 1).ToString() <> "0" Then
                                Month = ddlYMonth.SelectedValue.Substring(5, 2).ToString()
                            Else
                                Month = ddlYMonth.SelectedValue.Substring(6, 1).ToString()
                            End If

                            sql.Length = 0

                            sql.AppendFormat("Update {0} SET ", TableName)
                            If ItemValue <> "" Then
                                sql.AppendFormat("Mon{0}='{1}' ", Month, ItemValue)
                            Else
                                sql.AppendFormat("Mon{0}={1} ", Month, "NULL")
                            End If
                            sql.AppendFormat("where SapCode = '{0} '", MySapRow(0)("SapCode").ToString())
                            sql.AppendFormat("and SkuType = '{0}' ", ddlType.SelectedValue)
                            sql.AppendFormat("and ChannelID = '{0}' ", ddlCustomerGroup.SelectedValue)
                            sql.AppendFormat(";")

                            sql.AppendFormat("Insert Into {0}(SapCode,SkuType,ChannelID,Mon{1}) ", TableName, Month)
                            sql.AppendFormat("Select '{0}' ", MySapRow(0)("SapCode").ToString())
                            sql.AppendFormat(",'{0}' ", ddlType.SelectedValue)
                            sql.AppendFormat(",'{0}' ", ddlCustomerGroup.SelectedValue)
                            If ItemValue <> "" Then
                                sql.AppendFormat(",'{0}' ", ItemValue)
                            Else
                                sql.AppendFormat(",{0} ", "NULL")
                            End If
                            sql.AppendFormat("where not exists ( ")
                            sql.AppendFormat("select * from {0} ", TableName)
                            sql.AppendFormat("where SapCode='{0}' ", MySapRow(0)("SapCode").ToString())
                            sql.AppendFormat("and SkuType='{0}' ", ddlType.SelectedValue)
                            sql.AppendFormat("and ChannelID='{0}' ) ", ddlCustomerGroup.SelectedValue)
                            sql.AppendFormat(";")
                        Case "TradeStock"
                            MySapRow = ExistMappingDt.Select(String.Format("MarketCode='{0}'", MarketCode))

                            sql.Length = 0
                            sql.AppendFormat("Insert Into ProductTradeStock(SapCode,CustomerGroup,CustomerGroupName,Stock) ")
                            sql.AppendFormat("Select '{0}' ", MySapRow(0)("SapCode").ToString())
                            sql.AppendFormat(",'{0}' ", ddlCustomerGroup.SelectedValue)
                            sql.AppendFormat(",'{0}' ", ddlCustomerGroup.SelectedItem.Text)
                            If ItemValue <> "" Then
                                sql.AppendFormat(",'{0}' ", ItemValue)
                            Else
                                sql.AppendFormat(",{0} ", "NULL")
                            End If
                            sql.AppendFormat("where not exists( ")
                            sql.AppendFormat("select * from ProductTradeStock ")
                            sql.AppendFormat("where SapCode='{0}' ", MySapRow(0)("SapCode").ToString())
                            sql.AppendFormat("and CustomerGroup='{0}') ", ddlCustomerGroup.SelectedValue)
                            sql.AppendFormat(";")

                            sql.AppendFormat("Update ProductTradeStock SET ")
                            If ItemValue <> "" Then
                                sql.AppendFormat("Stock='{0}' ", ItemValue)
                            Else
                                sql.AppendFormat("Stock={0} ", "NULL")

                            End If
                            sql.AppendFormat("where SapCode ='{0}' ", MySapRow(0)("SapCode").ToString())
                            sql.AppendFormat("and CustomerGroup='{0}' ", ddlCustomerGroup.SelectedValue)
                            sql.AppendFormat(";")
                    End Select
                    ' MyObj.WriteLog(sql.ToString())
                    dat.AccData(sql)

                Next
                MyObj.ShowMessage("Upload Successful.Data Success Count:" & ImportData.Rows.Count)
            Else
                MyObj.ShowMessage("Upload Faild.")
            End If
        End If

        '把Error移到第一個
        Me.ImportData.Columns(Me.ImportData.Columns.Count - 1).SetOrdinal(0)

        ImportData.DefaultView.RowFilter = "convert(Error,System.String)<>''"
        ImportData = ImportData.DefaultView.ToTable

        Me.BindGV()

    End Sub
    Protected Sub gv_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gv.PageIndexChanging
        Me.gv.PageIndex = e.NewPageIndex
        Me.BindGV()
    End Sub
#End Region

#Region "繫結資料"

    Private Sub BindDDLDivision()
        Dim ddlControl As DropDownList = Me.ddlDivision
        Dim dvManager As New DivisionManager

        ddlControl.DataTextField = "Division"
        ddlControl.DataValueField = "Division"
        ddlControl.DataSource = dvManager.GetAllDivision()
        ddlControl.DataBind()
        ddlControl.Items.Insert(0, MyObj.GetDDLPleaseChooseItem())
    End Sub
    Protected Sub BindDDLCustomerGroup()
        ddlCustomerGroup.DataTextField = "ChannelName"
        ddlCustomerGroup.DataValueField = "Channel"

        Dim sql As New MyStringBuilder
        Dim dat As New clsData
        sql.Length = 0
        sql.AppendFormat("SELECT distinct Channel,ChannelName FROM ChannelView c ")
        sql.AppendFormat("left join Role r on c.Division=r.Division ")
        sql.AppendFormat("left join RoleUserMapping ru on r.RoleID =ru.RoleID ")
        sql.AppendFormat("where ru.UserID='{0}' ", clsAuth.GetUserID())
        sql.AppendFormat("and  r.Division='{0}' ", ddlDivision.SelectedValue)

        ddlCustomerGroup.DataSource = dat.GetData(sql)
        ddlCustomerGroup.DataBind()
        ddlCustomerGroup.Items.Insert(0, MyObj.GetDDLPleaseChooseItem())
    End Sub
    Protected Sub BindGV()
        gv.DataSource = ImportData
        gv.DataBind()
    End Sub
    Protected Sub BindType()
        ddlType.DataTextField = "Text"
        ddlType.DataValueField = "Value"

        ddlType.DataSource = MyObj.GetCodeMappingData("Product", "Import")
        ddlType.DataBind()
        ddlType.Items.Insert(0, MyObj.GetDDLPleaseChooseItem())
    End Sub
    Protected Sub BindYearMonth()
        Dim MonthCount As Integer = -(12 * (DateTime.Now.Year - 2008) + (DateTime.Now.Month - 1))
        For i As Integer = MonthCount To 0
            ddlYMonth.Items.Add(DateTime.Today.AddMonths(i).ToString("yyyy/MM"))

        Next
        ddlYMonth.SelectedValue = DateTime.Today.ToString("yyyy/MM")
    End Sub

    Protected Sub BindFileColumnName()
        Dim MyDt As New Data.DataTable
        Dim dc As New Data.DataColumn("FileColumnName", System.Type.[GetType]("System.String"))
        MyDt.Columns.Add(dc)

        Me.FileColumnName = MyDt
    End Sub
    Protected Sub BindDBColumnName()
        Dim MyDt As New Data.DataTable
        Dim dc As New Data.DataColumn("ColumnName", System.Type.[GetType]("System.String"))
        MyDt.Columns.Add(dc)

        Dim MyColumnFirst As String = ""
        Dim MyColumnSecond As String = ""
        Select Case ddlType.SelectedValue
            Case "SCMP"
                MyColumnFirst = "SapCode"
                MyColumnSecond = "ChannelCode"
            Case "SellOut"
                MyColumnFirst = "ChannelCode"
                MyColumnSecond = "sell-out"
            Case "TradeStock"
                MyColumnFirst = "ChannelCode"
                MyColumnSecond = "trade stock"
        End Select



        Dim row As Data.DataRow

        row = MyDt.NewRow()
        row("ColumnName") = MyColumnFirst
        MyDt.Rows.Add(row)

        row = MyDt.NewRow()
        row("ColumnName") = MyColumnSecond
        MyDt.Rows.Add(row)


        Me.DBColumnName = MyDt
    End Sub
#End Region

#Region "屬性"
    Property ImportData() As Data.DataTable
        Get
            Return ViewState("ImportData")
        End Get
        Set(ByVal value As Data.DataTable)
            ViewState("ImportData") = value
        End Set
    End Property
    Property DBColumnName() As Data.DataTable
        Get
            Return ViewState("DBColumnName")
        End Get
        Set(ByVal value As Data.DataTable)
            ViewState("DBColumnName") = value
        End Set
    End Property
    Property FileColumnName() As Data.DataTable
        Get
            Return ViewState("FileColumnName")
        End Get
        Set(ByVal value As Data.DataTable)
            ViewState("FileColumnName") = value
        End Set
    End Property
#End Region


End Class
